--region [dbo].[AddTopic]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[AddTopic]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[AddTopic]') IS NOT NULL
	DROP PROCEDURE [dbo].[AddTopic]
GO

CREATE PROCEDURE [dbo].[AddTopic]
	@Groupid int,
	@Title nvarchar(255),
	@Body nvarchar(max),
	@PostUserId int,
	@PostUserName nvarchar(50),
	@PostTime datetime,
	@UpdateTime datetime,
	@ReplyCount int,
	@Hits int,
	@IsBest int,
	@IsTop int,
	@IsGoodPost int,
	@ShowLevel int,
	@Sort nvarchar(50),
	@TitleColor int,
	@TopicFile nvarchar(256)
AS

INSERT INTO [Topic] (
	[Groupid],
	[Title],
	[Body],
	[PostUserId],
	[PostUserName],
	[PostTime],
	[UpdateTime],
	[ReplyCount],
	[Hits],
	[IsBest],
	[IsTop],
	[IsGoodPost],
	[ShowLevel],
	[Sort],
	[TitleColor],
	[TopicFile]
) VALUES (
	@Groupid,
	@Title,
	@Body,
	@PostUserId,
	@PostUserName,
	@PostTime,
	@UpdateTime,
	@ReplyCount,
	@Hits,
	@IsBest,
	@IsTop,
	@IsGoodPost,
	@ShowLevel,
	@Sort,
	@TitleColor,
	@TopicFile
)

GO
--endregion


--region [dbo].[UpdateTopic]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateTopic]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateTopic]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateTopic]
GO

CREATE PROCEDURE [dbo].[UpdateTopic]
	@Id int,
	@Groupid int,
	@PostUserId int,
	@Title nvarchar(255),
	@Body nvarchar(max),
	@UpdateTime datetime
AS

UPDATE [Topic] SET
	[Title] = @Title,
	[Body] = @Body,
	[UpdateTime] = @UpdateTime
WHERE
	[Id] = @Id AND [Groupid] = @Groupid AND [PostUserId] = @PostUserId

GO
--endregion


--region [dbo].[UpdateLatestReply]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateLatestReply]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateLatestReply]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateLatestReply]
GO

CREATE PROCEDURE [dbo].[UpdateLatestReply]
	@Id int,
	@LatestReplyUserId  int,
	@LatestReplyUserName  nvarchar(50),
	@LatestReplyId int,
	@LatestReplyTime   datetime
AS

UPDATE 
	[Topic] 
SET
	[LatestReplyUserId] = @LatestReplyUserId,
	[LatestReplyUserName] = @LatestReplyUserName,
	[LatestReplyId] = @LatestReplyId,
	[LatestReplyTime] = @LatestReplyTime,
	[ReplyCount] = [ReplyCount] + 1
WHERE
	[Id] = @Id 

GO
--endregion


--region [dbo].[ChangeLatestReply]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[ChangeLatestReply]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[ChangeLatestReply]') IS NOT NULL
	DROP PROCEDURE [dbo].[ChangeLatestReply]
GO

CREATE PROCEDURE [dbo].[ChangeLatestReply]
	@Id int,
	@LatestReplyUserId  int,
	@LatestReplyUserName  nvarchar(50),
	@LatestReplyId int,
	@LatestReplyTime   datetime
AS

UPDATE 
	[Topic] 
SET
	[LatestReplyUserId] = @LatestReplyUserId,
	[LatestReplyUserName] = @LatestReplyUserName,
	[LatestReplyId] = @LatestReplyId,
	[LatestReplyTime] = @LatestReplyTime,
	[ReplyCount] = [ReplyCount] - 1
WHERE
	[Id] = @Id 

GO
--endregion


--region [dbo].[UpdateLatestReplyTime]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateLatestReplyTime]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateLatestReplyTime]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateLatestReplyTime]
GO

CREATE PROCEDURE [dbo].[UpdateLatestReplyTime]
	@Id int,
	@LatestReplyTime   datetime
AS

UPDATE [Topic] SET
	[LatestReplyTime] = @LatestReplyTime
WHERE
	[Id] = @Id 

GO
--endregion


--region [dbo].[UpdateTopicHits]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateTopicHits]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateTopicHits]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateTopicHits]
GO

CREATE PROCEDURE [dbo].[UpdateTopicHits]
	@Id int
AS

UPDATE [Topic] SET
	[Hits] = [Hits] + 1
WHERE
	[Id] = @Id 

GO
--endregion


--region [dbo].[UpdateTopicLevel]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateTopicLevel]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateTopicLevel]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateTopicLevel]
GO

CREATE PROCEDURE [dbo].[UpdateTopicLevel]
	@Level int,
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
UPDATE [Topic] SET
	[ShowLevel] = ' + STR(@Level) + '
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[UpdateTopicSort]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateTopicSort]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateTopicSort]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateTopicSort]
GO

CREATE PROCEDURE [dbo].[UpdateTopicSort]
	@Sort nvarchar(50),
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
UPDATE [Topic] SET
	[Sort] = "' + @Sort + '"
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion

--region [dbo].[UpdateBestTopic]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateBestTopic]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateBestTopic]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateBestTopic]
GO

CREATE PROCEDURE [dbo].[UpdateBestTopic]
	@State int,
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
UPDATE [Topic] SET
	[IsBest] = ' + STR(@State) + '
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion

--region [dbo].[UpdateTitleColor]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateTitleColor]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateTitleColor]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateTitleColor]
GO

CREATE PROCEDURE [dbo].[UpdateTitleColor]
	@Color int,
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
UPDATE [Topic] SET
	[TiTleColor] = ' + STR(@Color) + '
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[DelTopic]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[DelTopic]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[DelTopic]') IS NOT NULL
	DROP PROCEDURE [dbo].[DelTopic]
GO

CREATE PROCEDURE [dbo].[DelTopic]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
DELETE FROM
	[Topic]
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetTopic]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetTopic]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetTopic]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetTopic]
GO

CREATE PROCEDURE [dbo].[GetTopic]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT
	[Topic].[Id],
	[Groupid],
	[Title],
	[Body],
	[PostUserId],
	[PostUserName],
	''PostGender'' = [Gender],
	''PostAvatar'' = [Avatar],
	''PostAge'' = DATEDIFF(year, [UserInfo].[Birthday], getdate()),
	[PostTime],
	[Topic].[UpdateTime],
	[LatestReplyUserId],
	[LatestReplyUserName],
	[LatestReplyTime],
	[ReplyCount],
	[Hits],
	[IsBest],
	[IsTop],
	[IsGoodPost],
	[ShowLevel],
	[TopicFile]
FROM
	[Topic], [userinfo]
WHERE
	[Topic].[PostUserId] = [userinfo].[Id] AND
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[CheckLasttestTopic]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[CheckLasttestTopic]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[CheckLasttestTopic]') IS NOT NULL
	DROP PROCEDURE [dbo].[CheckLasttestTopic]
GO

CREATE PROCEDURE [dbo].[CheckLasttestTopic]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT
	[Id]
FROM
	[Group]
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetTopicList]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetTopicList]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetTopicList]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetTopicList]
GO

CREATE PROCEDURE [dbo].[GetTopicList]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT(Id) 
FROM [Topic]
	'
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT *
FROM
	( SELECT 
	[Topic].[Id],
	[Groupid],
	[Title],
	[Body],
	[PostUserId],
	[PostUserName],
	''PostGender'' = [Gender],
	''PostAvatar'' = [Avatar],
	''PostAge'' = DATEDIFF(year, [UserInfo].[Birthday], getdate()),
	[PostTime],
	[Topic].[UpdateTime],
	[LatestReplyId],
	[LatestReplyUserId],
	[LatestReplyUserName],
	[LatestReplyTime],
	[ReplyCount],
	[Hits],
	[IsBest],
	[IsTop],
	[IsGoodPost],
	[ShowLevel],
	[TitleColor],
	ROW_NUMBER() OVER ( ORDER BY ' + @OrderByExpression + ' ) AS SerialNumber
FROM
	[Topic], [userinfo]
WHERE
	[Topic].[PostUserId] = [userinfo].[Id] 
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
END

SET @SQL = @SQL + ') AS tblTmp  
	WHERE tblTmp.SerialNumber > '
	+ STR((@PageIndex-1)*@PageSize) + ' AND 
	tblTmp.SerialNumber <= '
	+ STR(@PageIndex*@PageSize)

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetTopTopics]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetTopTopics]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetTopTopics]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetTopTopics]
GO

CREATE PROCEDURE [dbo].[GetTopTopics]
	@N int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT TOP ' + STR(@N) + '
	[Id],
	[Groupid],
	[Title],
	[Body],
	[PostUserId],
	[PostUserName],
	[PostTime],
	[UpdateTime],
	[LatestReplyUserId],
	[LatestReplyUserName],
	[LatestReplyTime],
	[ReplyCount],
	[Hits],
	[IsBest],
	[IsTop],
	[IsGoodPost],
	[ShowLevel]
FROM
	[Topic]
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END
	
IF @OrderByExpression <> ''
BEGIN
	SET @SQL = @SQL + ' ORDER BY ' + @OrderByExpression 
END	

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetBBSTopTopics]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetBBSTopTopics]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetBBSTopTopics]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetBBSTopTopics]
GO

CREATE PROCEDURE [dbo].[GetBBSTopTopics]
	@N int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT TOP ' + STR(@N) + '
	[Topic].[Id],
	[Groupid],
	[Title],
	[Body],
	[PostUserId],
	[PostUserName],
	[PostTime],
	[UpdateTime],
	[LatestReplyUserId],
	[LatestReplyUserName],
	[LatestReplyTime],
	[Topic].[ReplyCount],
	[Topic].[Hits],
	[IsBest],
	[IsTop],
	[IsGoodPost],
	[ShowLevel]
FROM
	[Topic], [Group]
WHERE
	[Group].[Id] = [Topic].[Groupid] AND
	[Group].[GroupType] > 0
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
END
	
IF @OrderByExpression <> ''
BEGIN
	SET @SQL = @SQL + ' ORDER BY ' + @OrderByExpression 
END	

EXEC sp_executesql @SQL

GO
--endregion

